from sqlalchemy.ext.compiler import compiles
import pandas as pd
import time
from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql.expression import Insert
from sqlalchemy.dialects.mysql import insert
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, text
import platform
import json
import requests
import uuid
import re
from datetime import datetime
from urllib.parse import quote_plus
import traceback









def format_url(url):
    if not url:
        return None
    
    # Remove any whitespace
    url = url.strip()
    
    if not url:
        return None
    
    # Remove any existing http:// or https:// prefix
    url = re.sub(r'^https?://', '', url)
    
    # Create different URL variations
    url_variations = []
    
    # Original domain (with www if present)
    url_variations.append(f"http://{url}")
    url_variations.append(f"https://{url}")
    
    # Remove www. if present
    if url.startswith('www.'):
        url_without_www = url[4:]
        url_variations.append(f"http://{url_without_www}")
        url_variations.append(f"https://{url_without_www}")
    else:
        # Add www. if not present
        url_variations.append(f"http://www.{url}")
        url_variations.append(f"https://www.{url}")
    
    return url_variations

def create_collection(link_url):
    if not link_url:
        return {'success': False, 'error': 'No URL provided'}
        
    url = "http://106.53.97.117:3000/api/core/dataset/collection/create/link"
    
    # 使用新的API密钥
    API_KEY = "fastgpt-kspcWF3r4XnI9leHXcZqE6tkY2LKGx7p9WgT3fMD9Ywtn06zyQ36oFdo4Vb"
    
    headers = {
        "Accept": "application/json, text/plain, */*",
        "Content-Type": "application/json",
        "Authorization": f"Bearer {API_KEY}",  # 使用Bearer认证而不是Cookie
    }
     
    # 更新为符合API要求的payload结构
    print(link_url,"------------当前访问的网址------------------")
    payload =  {
            "parentId": "",
            "datasetId": "67e3b1d27a46e4ccc2879bfd",
            "name": f"{link_url}",
            "customPdfParse": "false",
            "trainingType": "chunk",
            "imageIndex": "false",
            "autoIndexes": "false",
            "chunkSize": 512,
            "chunkSplitter": "",
            "link": "http://moroccofashiontex.net/",
            "metadata": {
                "webPageSelector": ""
            }
        }
    
    try:
        # 添加最大重试次数和重试延迟
        max_retries = 3
        retry_delay = 2  # 秒
        
        for retry_count in range(max_retries):
            try:
                # 增加超时设置，避免长时间等待
                response = requests.post(
                    url, 
                    json=payload, 
                    headers=headers, 
                    timeout=(10, 30)  # 连接超时10秒，读取超时30秒
                )
                print(f"创建集合API响应状态码: {response.status_code}")
                print(f"响应内容: {response.text[:200]}...")  # 只打印前200个字符避免日志过长
                
                # 检查响应状态码
                response.raise_for_status()
                
                # 成功请求，解析响应
                response_data = response.json()
                collection_id = response_data.get('data', {}).get('collectionId')
                
                if collection_id:
                    return {'success': True, 'collectionId': collection_id}
                else:
                    print("响应中未找到collection_id")
                    if retry_count < max_retries - 1:
                        wait_time = retry_delay * (2 ** retry_count)
                        print(f"等待 {wait_time} 秒后重试...")
                        time.sleep(wait_time)
                        continue
                    return {'success': False, 'error': '未找到 collectionId'}
                
            except requests.exceptions.RequestException as e:
                print(f"创建集合请求失败 (尝试 {retry_count+1}/{max_retries}): {str(e)}")
                
                # 如果不是最后一次尝试，则等待后重试
                if retry_count < max_retries - 1:
                    wait_time = retry_delay * (2 ** retry_count)  # 指数退避
                    print(f"等待 {wait_time} 秒后重试...")
                    time.sleep(wait_time)
                else:
                    print(f"达到最大重试次数 ({max_retries})，放弃请求")
                    return {'success': False, 'error': f'请求失败: {str(e)}'}
                    
            except json.JSONDecodeError as e:
                print(f"解析响应JSON失败 (尝试 {retry_count+1}/{max_retries}): {str(e)}")
                
                # 如果不是最后一次尝试，则等待后重试
                if retry_count < max_retries - 1:
                    wait_time = retry_delay * (2 ** retry_count)
                    print(f"等待 {wait_time} 秒后重试...")
                    time.sleep(wait_time)
                else:
                    print(f"达到最大重试次数 ({max_retries})，放弃请求")
                    return {'success': False, 'error': f'解析响应失败: {str(e)}'}
                    
        # 如果所有重试都失败了（虽然应该不会执行到这里）
        return {'success': False, 'error': '所有重试尝试都失败了'}
    
    except Exception as e:
        print(f"创建集合时发生未预期错误: {str(e)}")
        return {'success': False, 'error': f'未知错误: {str(e)}'}

def try_create_collection(url_variations):
    if not url_variations:
        return {'success': False, 'error': '没有提供有效的URL变体'}
        
    last_error = None
    last_tried_url = None
    
    for url in url_variations:
        print(f"尝试访问: {url}")
        result = create_collection(url)
        
        if result['success']:
            print(f"成功创建集合，URL: {url}")
            return {'success': True, 'collectionId': result['collectionId'], 'working_url': url}
        
        last_error = result.get('error', '未知错误')
        last_tried_url = url
        print(f"URL {url} 尝试失败: {last_error}")
        
        # 在重试之间添加短暂延迟
        time.sleep(1)
    
    print(f"所有URL变体都尝试失败，最后错误: {last_error}")
    return {
        'success': False, 
        'error': '所有URL变体都尝试失败', 
        'last_error': last_error,
        'last_tried_url': last_tried_url
    }

class MySQLHelper:
    def __init__(self):
        if platform.system() == 'Linux':
            self.host = "106.53.97.117"
            self.port = 3306
            self.user = "my_user"
            self.password = "my_password"
            self.db = "my_database"
        else:
            self.host = "106.53.97.117"
            self.port = 3306
            self.user = "my_user"
            self.password = "my_password"
            self.db = "my_database"
        self.charset = "utf8mb4"
        self.engine = None
        self.Session = None
        self._create_engine()

    def _create_engine(self):
        # 创建数据库连接引擎，使用连接池
        connection_string = f"mysql+pymysql://{self.user}:{self.password}@{self.host}:{self.port}/{self.db}?charset={self.charset}"
        self.engine = create_engine(connection_string, pool_size=10, max_overflow=1490, pool_recycle=3600)
        self.Session = sessionmaker(bind=self.engine)

    def get_session(self):
        # 获取数据库会话
        return self.Session()

    def mysql_replace_into(self, table, conn, keys, data_iter):
        # 定义一个编译器扩展，用于将 INSERT INTO 替换为 REPLACE INTO
        @compiles(Insert)
        def replace_string(insert, compiler, **kw):
            s = compiler.visit_insert(insert, **kw)
            s = s.replace("INSERT INTO", "REPLACE INTO")
            return s

        # 准备数据
        data = [dict(zip(keys, row)) for row in data_iter]
        
        # 执行插入操作
        conn.execute(table.table.insert(), data)



    def _df_res_to_db(self, pd, table_name):
        pd.to_sql(
            name=table_name,
            con=self.engine.connect(),
            index=False,
            chunksize=2000,
            if_exists='append', method=self.mysql_replace_into)






    def execute_select(self, sql):
        time_start = time.time()
        with self.engine.connect() as conn:
            # 使用 text() 函数将 SQL 语句转换为可执行对象
            sql_text = text(sql)
            result = conn.execute(sql_text)
            data = []
            for row in result:
                # 使用 row._mapping 将 RowProxy 转换为字典
                data.append(dict(row._mapping))
            time_end = time.time()
            # print(f"执行时间：{time_end - time_start}")
            return data

    def execute_value(self, sql):
        with self.engine.connect() as conn:
            result = conn.execute(sql)
            return result.fetchone()

    def execute_edit(self, sql):
        with self.engine.connect() as conn:
            result = conn.execute(sql)
            conn.commit()
            return result.rowcount
    def execute_charts(self, sql):
        """
        :return 图表数据
        """
        with self.engine.connect() as conn:
            result = conn.execute(sql)
            target = {}
            for j in range(len(result.keys())):
                data = [row[j] for row in result]
                target[result.keys()[j]] = data
            return target

    def execute_datatable(self, sql):
        with self.engine.connect() as conn:
            result = conn.execute(sql)
            datas = []
            for row in result:
                target = list(row)
                datas.append(target)
            data = {"data": datas}
            return data

    def execute_cloud(self, sql):
        with self.engine.connect() as conn:
            result = conn.execute(sql)
            data = {row[0]: row[1] for row in result}
            return data


db = MySQLHelper()

def process_exhibition(exhibition_id, website):
    """处理单个展会的AI链接状态"""
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    try:
        if not website:
            # 没有官网地址的情况
            update_sql = f"""
                UPDATE exhibitions 
                SET ai_link_status = 3, 
                    ai_link_time = '{current_time}' 
                WHERE id = {exhibition_id}
            """
            with db.engine.connect() as conn:
                with conn.begin():
                    conn.execute(text(update_sql))
            print(f"展会ID {exhibition_id}: 无官网地址，状态设置为3")
            return True
            
        # 获取所有可能的URL格式
        url_variations = format_url(website)
        if not url_variations:
            # URL格式无效的情况
            update_sql = f"""
                UPDATE exhibitions 
                SET ai_link_status = 2,
                    ai_link = '{website.replace("'", "''")}',
                    ai_link_time = '{current_time}'
                WHERE id = {exhibition_id}
            """
            with db.engine.connect() as conn:
                with conn.begin():
                    conn.execute(text(update_sql))
            print(f"展会ID {exhibition_id}: URL格式无效，状态设置为2")
            return True
            
        # 尝试不同的URL格式
        result = try_create_collection(url_variations)

        print(result,"------------当前访问的网址------------------")
        
        if result['success']:
            # 录入成功
            working_url = result['working_url'].replace("'", "''")  # 转义单引号
            update_sql = f"""
                UPDATE exhibitions 
                SET ai_link_status = 1,
                    ai_link = '{working_url}',
                    ai_link_time = '{current_time}'
                WHERE id = {exhibition_id}
            """
            with db.engine.connect() as conn:
                with conn.begin():
                    conn.execute(text(update_sql))
            print(f"展会ID {exhibition_id}: 录入成功，状态设置为1")
            return True
        else:
            # 录入失败
            last_tried_url = result.get('last_tried_url', '')
            if last_tried_url:
                last_tried_url = last_tried_url.replace("'", "''")  # 转义单引号
            error_msg = result.get('last_error', '未知错误')
            
            update_sql = f"""
                UPDATE exhibitions 
                SET ai_link_status = 2,
                    ai_link = '{last_tried_url}',
                    ai_link_time = '{current_time}'
                WHERE id = {exhibition_id}
            """
            with db.engine.connect() as conn:
                with conn.begin():
                    conn.execute(text(update_sql))
            print(f"展会ID {exhibition_id}: 录入失败，状态设置为2，错误: {error_msg}")
            return False
            
    except Exception as e:
        print(f"处理展会ID {exhibition_id} 时发生错误: {str(e)}")
        try:
            # 尝试更新状态为失败
            error_msg = str(e).replace("'", "''")[:200]  # 限制错误消息长度
            update_sql = f"""
                UPDATE exhibitions 
                SET ai_link_status = 2,
                    ai_link_time = '{current_time}',
                WHERE id = {exhibition_id}
            """
            with db.engine.connect() as conn:
                with conn.begin():
                    conn.execute(text(update_sql))
            print(f"已将展会ID {exhibition_id} 状态更新为失败")
        except Exception as update_error:
            print(f"尝试更新状态时也失败: {str(update_error)}")
        return False

def main():
    try:
        print("="*50)
        print("开始处理展会链接入库")
        print("="*50)
        
        # 获取需要处理的展会数据（ai_link_status = 0）
        sql = """
            SELECT id, chinese_name, official_website 
            FROM exhibitions 
            WHERE ai_link_status = 0 OR ai_link_status IS NULL
        """
        exhibitions = db.execute_select(sql)
        
        total_count = len(exhibitions)
        success_count = 0
        failed_count = 0
        
        print(f"共找到 {total_count} 条需要处理的记录")
        
        if total_count == 0:
            print("没有需要处理的记录，程序结束")
            return
        
        start_time = time.time()
        
        for index, exhibition in enumerate(exhibitions, 1):
            exhibition_id = exhibition['id']
            chinese_name = exhibition['chinese_name']
            website = exhibition['official_website']
            
            print(f"\n[{index}/{total_count}] 正在处理第 {index} 条记录")
            print(f"展会ID: {exhibition_id}")
            print(f"展会名称: {chinese_name}")
            print(f"官网地址: {website}")
            
            # 处理单个展会记录
            result = process_exhibition(exhibition_id, website)
            if result:
                success_count += 1
            else:
                failed_count += 1
            
            # 每处理10条记录显示一次进度统计
            if index % 10 == 0 or index == total_count:
                elapsed_time = time.time() - start_time
                avg_time = elapsed_time / index
                remaining_count = total_count - index
                est_remaining_time = avg_time * remaining_count
                
                print(f"\n--- 处理进度 ---")
                print(f"已处理: {index}/{total_count} ({index/total_count*100:.1f}%)")
                print(f"成功: {success_count}, 失败: {failed_count}")
                print(f"已用时间: {elapsed_time:.1f}秒")
                if remaining_count > 0:
                    print(f"预计剩余时间: {est_remaining_time:.1f}秒 (约 {est_remaining_time/60:.1f}分钟)")
            
            # 添加短暂延迟避免请求过快
            time.sleep(2)
            
        # 最终统计
        total_time = time.time() - start_time
        print("\n"+"="*50)
        print("处理完成统计")
        print("="*50)
        print(f"总记录数: {total_count}")
        print(f"成功处理: {success_count} ({success_count/total_count*100:.1f}%)")
        print(f"处理失败: {failed_count} ({failed_count/total_count*100:.1f}%)")
        print(f"总耗时: {total_time:.1f}秒 (约 {total_time/60:.1f}分钟)")
        print("="*50)
            
    except Exception as e:
        print(f"程序执行过程中发生错误: {str(e)}")
        print(traceback.format_exc())
    finally:
        print("\n所有记录处理完成!")

if __name__ == "__main__":
    main()